using System;  
using System.Configuration;  
using System.Data;  
using System.Data.OleDb;  
using System.IO;
 
namespace Geekees.App.Cashbook.Lib.Helper
{  
	public sealed class SqlHelper  
	{  
		// Methods  
		public SqlHelper()  
		{  
			string path = System.AppDomain.CurrentDomain.BaseDirectory;
			string connStrPath = string.Format( "Provider=Microsoft.Jet.OLEDB.4.0;Data source={0}db\\cashbook.mdb" , path );

			this.m_bIsInTransaction = false;  
			this.m_Timeout = 30;
			//this.m_objConn = new OleDbConnection(ConfigurationSettings.AppSettings["Geekees.Cashbook.ConnectString"]);  
			this.m_objConn = new OleDbConnection( connStrPath );  
		}  
 
		public SqlHelper(bool bOpenNow)  
		{  
			string path = System.AppDomain.CurrentDomain.BaseDirectory;
			string connStrPath = string.Format( "Provider=Microsoft.Jet.OLEDB.4.0;Data source={0}db\\cashbook.mdb" , path );

			this.m_bIsInTransaction = false;  
			this.m_Timeout = 30;
			//this.m_objConn = new OleDbConnection(ConfigurationSettings.AppSettings["Geekees.Cashbook.ConnectString"]);  
			this.m_objConn = new OleDbConnection( connStrPath );  
			if (bOpenNow)  
			{  
				this.m_objConn.Open();  
			}  
		}  
 
		public SqlHelper(string strConnectionString)  
		{  
			this.m_bIsInTransaction = false;  
			this.m_Timeout = 30;  
			this.m_objConn = new OleDbConnection(strConnectionString);  
			this.m_objConn.Open();  
		}  
 
		public SqlHelper(string strConnectionString, bool bOpenNow)  
		{  
			this.m_bIsInTransaction = false;  
			this.m_Timeout = 30;  
			this.m_objConn = new OleDbConnection(strConnectionString);  
			if (bOpenNow)  
			{  
				this.m_objConn.Open();  
			}  
		}  
 
		public void BeginTransaction()  
		{  
			if (!this.m_bIsInTransaction)  
			{  
				this.m_objCommand = new OleDbCommand();  
				this.m_objCommand.Connection = this.m_objConn;  
				this.m_objTransaction = this.m_objConn.BeginTransaction();  
				if (this.m_Timeout >= 30)  
				{  
					this.m_objCommand.CommandTimeout = this.m_Timeout;  
				}  
				this.m_objCommand.Transaction = this.m_objTransaction;  
				this.m_bIsInTransaction = true;  
			}  
		}  
 
       
 
		public void Close()  
		{  
			if (this.m_objConn.State != ConnectionState.Closed)  
			{  
				this.m_objConn.Close();  
			}  
		}  
 
		public void Commit()  
		{  
			if (this.m_bIsInTransaction)  
			{  
				this.m_objTransaction.Commit();  
				this.m_bIsInTransaction = false;  
				this.m_objCommand.Dispose();  
			}  
		}  
 
     
 
		public void Dispose()  
		{  
			if (this.m_objConn.State != ConnectionState.Closed)  
			{  
				this.m_objConn.Close();  
			}  
			this.m_objConn.Dispose();  
		}  
 
		public DataSet ExecuteDataSet(string strSQL)  
		{  
         
			DataSet set1 = new DataSet();  
			OleDbCommand command1 = new OleDbCommand(strSQL, this.m_objConn);  
			if (this.m_Timeout >= 30)  
			{  
				command1.CommandTimeout = this.m_Timeout;  
			}  
			OleDbDataAdapter adapter1 = new OleDbDataAdapter(command1);  
			adapter1.Fill(set1);  
			command1.Dispose();  
			adapter1.Dispose();  
			return set1;  
		}

		public DataTable ExecuteDataTable(string strSQL)  
		{  
			DataSet set1 = ExecuteDataSet( strSQL );
			if( set1.Tables.Count > 0 )
				return set1.Tables[0];
			else
				return null;
		} 
 
		public DataSet ExecuteDataSet(string strSQL, string strTable)  
		{  
			DataSet set1 = new DataSet();  
			OleDbCommand command1 = new OleDbCommand(strSQL, this.m_objConn);  
			if (this.m_Timeout >= 30)  
			{  
				command1.CommandTimeout = this.m_Timeout;  
			}  
			OleDbDataAdapter adapter1 = new OleDbDataAdapter(command1);  
			adapter1.Fill(set1, strTable);  
			command1.Dispose();  
			adapter1.Dispose();  
			return set1;  
		}  
 
		public OleDbDataReader ExecuteReader(string strSQL)  
		{  
			OleDbCommand command1 = new OleDbCommand(strSQL, this.m_objConn); 
			if (this.m_Timeout >= 30)  
			{  
				command1.CommandTimeout = this.m_Timeout;  
			}  
			OleDbDataReader reader1 = command1.ExecuteReader();  
			command1.Dispose();  
			return reader1;  
		}  
 
		public bool ExecuteSQL(string strSQL)  
		{  
			int i = -1;
			if (!this.m_bIsInTransaction)  
			{  
				OleDbCommand command1 = new OleDbCommand(strSQL, this.m_objConn);  
				if (this.m_Timeout >= 30)  
				{  
					command1.CommandTimeout = this.m_Timeout;  
				}  
				i = command1.ExecuteNonQuery();  
				command1.Dispose();  
			}  
			else  
			{  
				this.m_objCommand.CommandText = strSQL;  
				i = this.m_objCommand.ExecuteNonQuery();  
			}
			return i >= 0;
		}  

		/// <summary>
		/// ryan use
		/// </summary>
		/// <param name="cmd"></param>
		/// <returns></returns>
		public bool ExecuteSQL( OleDbCommand cmd )  
		{  
			int i = -1;
			if (!this.m_bIsInTransaction)  
			{  
				OleDbCommand command1 = cmd;
				command1.Connection = this.m_objConn;
				//new OleDbCommand(strSQL, this.m_objConn);  
				if (this.m_Timeout >= 30)  
				{  
					command1.CommandTimeout = this.m_Timeout;  
				}  
				i = command1.ExecuteNonQuery();  
				command1.Dispose();  
			}  
			else  
			{  
				//warning... cmd include parameters
				this.m_objCommand.CommandText = cmd.CommandText;  
				i = this.m_objCommand.ExecuteNonQuery();  
			}
			return i >= 0;
		} 

		/// <summary>
		/// ryan use
		/// </summary>
		/// <param name="strSQL"></param>
		/// <returns></returns>
		public DataSet ExecuteDataSet( OleDbCommand cmd )  
		{  
         
			DataSet set1 = new DataSet();  
			OleDbCommand command1 = cmd;//new OleDbCommand(strSQL, this.m_objConn); 
			command1.Connection = this.m_objConn; 
			if (this.m_Timeout >= 30)  
			{  
				command1.CommandTimeout = this.m_Timeout;  
			}  
			OleDbDataAdapter adapter1 = new OleDbDataAdapter(command1);  
			adapter1.Fill(set1);  
			command1.Dispose();  
			adapter1.Dispose();  
			return set1;  
		}

		/// <summary>
		/// ryan use
		/// </summary>
		/// <param name="cmd"></param>
		/// <returns></returns>
		public DataTable ExecuteDataTable( OleDbCommand cmd )  
		{  
         
			DataSet set1 = new DataSet();  
			OleDbCommand command1 = cmd;//new OleDbCommand(strSQL, this.m_objConn);  
			command1.Connection = this.m_objConn;
			if (this.m_Timeout >= 30)  
			{  
				command1.CommandTimeout = this.m_Timeout;  
			}  
			OleDbDataAdapter adapter1 = new OleDbDataAdapter(command1);  
			adapter1.Fill(set1);  
			command1.Dispose();  
			adapter1.Dispose();  

			if( set1.Tables.Count > 0 )
				return set1.Tables[0];
			else
				return null;
		}

		/// <summary>
		/// ryan use
		/// </summary>
		/// <param name="cmd"></param>
		/// <returns></returns>
		public object ExecuteScalar( OleDbCommand cmd )
		{
			object o = null;
			if (!this.m_bIsInTransaction)  
			{  
				OleDbCommand command1 = cmd;
				command1.Connection = this.m_objConn;
				//new OleDbCommand(strSQL, this.m_objConn);  
				if (this.m_Timeout >= 30)  
				{  
					command1.CommandTimeout = this.m_Timeout;  
				}  
				o = command1.ExecuteScalar();  
				command1.Dispose();  
			}  
			else  
			{  
				//warning... cmd include parameters
				this.m_objCommand.CommandText = cmd.CommandText;  
				o = this.m_objCommand.ExecuteScalar();  
			}
			return o;
		}

		public int ExecuteNonQuery( OleDbCommand cmd )
		{
			int i = 0;
			if (!this.m_bIsInTransaction)  
			{  
				OleDbCommand command1 = cmd;
				command1.Connection = this.m_objConn;
				//new OleDbCommand(strSQL, this.m_objConn);  
				if (this.m_Timeout >= 30)  
				{  
					command1.CommandTimeout = this.m_Timeout;  
				}  
				i = command1.ExecuteNonQuery();  
				command1.Dispose();  
			}  
			else  
			{  
				//warning... cmd include parameters
				this.m_objCommand.CommandText = cmd.CommandText;  
				i = this.m_objCommand.ExecuteNonQuery();  
			}
			return i;
		}
 
		public int FillDataSet(ref DataSet objDataSet, string strSQL)  
		{  
   
			OleDbCommand command1 = new OleDbCommand(strSQL, this.m_objConn);  
			if (this.m_Timeout >= 30)  
			{  
				command1.CommandTimeout = this.m_Timeout;  
			}  
			OleDbDataAdapter adapter1 = new OleDbDataAdapter(command1);  
			int num1 = adapter1.Fill(objDataSet);  
			command1.Dispose();  
			adapter1.Dispose();  
			return num1;  
		}  
 
		public int FillDataSet(ref DataSet objDataSet, string strSQL, string strTable)  
		{  
   
			OleDbCommand command1 = new OleDbCommand(strSQL, this.m_objConn);  
			if (this.m_Timeout >= 30)  
			{  
				command1.CommandTimeout = this.m_Timeout;  
			}  
			OleDbDataAdapter adapter1 = new OleDbDataAdapter(command1);  
			int num1 = adapter1.Fill(objDataSet, strTable);  
			command1.Dispose();  
			adapter1.Dispose();  
			return num1;  
		}  
 
//		~CDBConn()  
//		{  
//		}  
 
		public bool Lock(string[] strArrTableName)  
		{  
			return true;  
			//   return this.m_objSync.Lock(strArrTableName);  
		}  
   
		public void Open()  
		{  
			if (this.m_objConn.State != ConnectionState.Open)  
			{  
				this.m_objConn.Open();  
			}  
		}  
   
		public void Rollback()  
		{  
			if (this.m_bIsInTransaction)  
			{  
				this.m_objTransaction.Rollback();  
				this.m_bIsInTransaction = false;  
				this.m_objCommand.Dispose();  
			}  
		}  
 
		public void Update(string strSelectSQL, DataSet objDataSet)  
		{  
			OleDbDataAdapter adapter1;  
			OleDbCommandBuilder builder1;  
			if (!this.m_bIsInTransaction)  
			{  
				OleDbCommand command1 = new OleDbCommand(strSelectSQL, this.m_objConn);  
				if (this.m_Timeout >= 30)  
				{  
					command1.CommandTimeout = this.m_Timeout;  
				}  
				adapter1 = new OleDbDataAdapter(command1);  
				builder1 = new OleDbCommandBuilder(adapter1);  
				adapter1.InsertCommand = builder1.GetInsertCommand();  
				adapter1.DeleteCommand = builder1.GetDeleteCommand();  
				adapter1.UpdateCommand = builder1.GetUpdateCommand();  
				adapter1.Update(objDataSet);  
				builder1.Dispose();  
				command1.Dispose();  
				adapter1.Dispose();  
			}  
			else  
			{  
				adapter1 = new OleDbDataAdapter(this.m_objCommand);  
				builder1 = new OleDbCommandBuilder(adapter1);  
				adapter1.InsertCommand = builder1.GetInsertCommand();  
				adapter1.DeleteCommand = builder1.GetDeleteCommand();  
				adapter1.UpdateCommand = builder1.GetUpdateCommand();  
				adapter1.Update(objDataSet);  
				builder1.Dispose();  
				adapter1.Dispose();  
			}  
		}  
 
 
		// Properties  
		public ConnectionState State  
		{  
			get  
			{  
				return this.m_objConn.State;  
			}  
		}  
 
		public int Timeout  
		{  
			get  
			{  
				return this.m_Timeout;  
			}  
			set  
			{  
				if (value < 1)  
				{  
					this.m_Timeout = 1;  
				}  
				else  
				{  
					this.m_Timeout = value;  
				}  
			}  
		}  
 
 
		// Fields  
		private bool m_bIsInTransaction;  
		private OleDbCommand m_objCommand;  
		private OleDbConnection m_objConn;  
		private OleDbTransaction m_objTransaction;  
		private int m_Timeout;  
	}  
} 